﻿/*---------------------------------------------------------------------------------
-----------------***DaLianQianYi***-----------------------
-----------------作者：大连仟亿科技------------------------------------------------------
-----------------联系作者：www.qianyikeji.com----------------------------------
-----------------时间：2018/8/9 13:54:37------------------------------------------
----------------------------------------------------------------------------------*/


using Donal.Xms.Common;
using Donal.Xms.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;

namespace Donal.Xms.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public partial class cash_flow
    {
        private string databaseprefix; //数据库表名前缀
        public cash_flow(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }

        #region 基本方法================================
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from  " + databaseprefix + "cash_flow");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.cash_flow model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数
                                                     //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("insert into " + databaseprefix + "cash_flow(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");//拼接字段
                        str2.Append("@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), paras.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(SqlConnection conn, SqlTransaction trans, Model.cash_flow model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数
                                                     //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("insert into " + databaseprefix + "cash_flow(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");//拼接字段
                        str2.Append("@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");

            object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());

            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.cash_flow model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("update  " + databaseprefix + "cash_flow set ");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" where id=@id ");
            paras.Add(new SqlParameter("@id", model.id));
            return DbHelperSQL.ExecuteSql(strSql.ToString(), paras.ToArray()) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  " + databaseprefix + "cash_flow ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.cash_flow GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.cash_flow model = new Model.cash_flow();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "cash_flow");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.cash_flow GetMaxModel(string erpCode, int cash_flow_type)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.cash_flow model = new Model.cash_flow();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "cash_flow");
            strSql.Append(" where company_code=@company_code and flow_category=@flow_category order by id desc");
            SqlParameter[] parameters = {
                        new SqlParameter("@company_code", SqlDbType.VarChar,50),
                new SqlParameter("@flow_category", SqlDbType.Int,10)};
            parameters[0].Value = erpCode;
            parameters[1].Value = cash_flow_type;

            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.cash_flow GetMaxModel(SqlConnection conn, SqlTransaction trans, string erpCode, int cash_flow_type)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.cash_flow model = new Model.cash_flow();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "cash_flow");
            strSql.Append(" where company_code=@company_code and flow_category=@flow_category order by add_time desc,id desc");
            SqlParameter[] parameters = {
                        new SqlParameter("@company_code", SqlDbType.VarChar,50),
                new SqlParameter("@flow_category", SqlDbType.Int,10)};
            parameters[0].Value = erpCode;
            parameters[1].Value = cash_flow_type;

            DataTable dt = DbHelperSQL.Query(conn, trans, strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM " + databaseprefix + "cash_flow");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "cash_flow");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion

        #region 扩展方法================================
        /// <summary>
        /// 返回指定列
        /// </summary>
        public string GetValue(int id, string fileName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 " + fileName + " from " + databaseprefix + "cash_flow");
            strSql.Append(" where id=" + id);
            string title = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(title))
            {
                return "";
            }
            return title;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "cash_flow set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 将对象转换实体
        /// </summary>
        public Model.cash_flow DataRowToModel(DataRow row)
        {
            Model.cash_flow model = new Model.cash_flow();
            if (row != null)
            {
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //查找实体是否存在列表相同的公共属性
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        proInfo.SetValue(model, row[i], null);//用索引值设置属性值
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 获取去重后的会员等级
        /// </summary>
        public DataSet GetDistinctGroupList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();

            if (strWhere == "")
            {
                strSql.Append("select distinct company_group from " + databaseprefix + "cash_flow order by company_group ");
            }
            else
            {
                strSql.Append("select distinct company_group from " + databaseprefix + "cash_flow where " + strWhere + " order by company_group ");
            }

            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 处理现金、积分、月结流水方法
        /// </summary>
        /// <param name="companyCode">会员编号</param>
        /// <param name="flowCase">1购买商品2退货3财务修改4营销活动5推广赠送6账户充值7网上付款8交易退款9信用重置10退货退分</param>
        /// <param name="flowType">0现金流水 2月结流水</param>
        /// <param name="operators">操作人</param>
        /// <param name="orderNo">订单编号</param>
        /// <param name="remark">备注</param>
        /// <param name="value">金额</param>
        /// <param name="conn">数据库已打开的链接</param>
        /// <param name="trans">数据处理已打开的事务</param>
        /// <param name="isUpdateErpBuddy">是否更新会员表（只有在会员使用微信、支付宝线上支付购物时，才不会更新会员的账户余额，不会产生实际上的流水。）</param>
        /// <returns>返回空为处理成功，否则为失败原因。</returns>
        public string FlowAction(SqlConnection conn, SqlTransaction trans, decimal value, string companyCode,
            string operators, int _order_id, string orderNo, string remark, int flowType, int flowCase, bool isUpdateErpBuddy)
        {
            company buddyDal = new company(databaseprefix);
            Model.company company = buddyDal.GetModel(conn, trans, companyCode);
            if (company == null)
            {
                return "没有找到会员编号为 " + companyCode + " 的会员信息！";
            }

            Model.cash_flow cash_Flow = new Model.cash_flow();
            cash_Flow.cash_middle = value;
            cash_Flow.company_code = companyCode;
            cash_Flow.operators = operators;
            cash_Flow.order_no = orderNo;
            cash_Flow.remark = remark;
            cash_Flow.flow_category = flowType;
            cash_Flow.company_type = company.user_type == 1 ? "月结会员" : "普通会员";
            cash_Flow.company_group = new user_groups(databaseprefix).GetTitle(conn, trans, company.group_id);
            cash_Flow.company = "";

            switch (flowCase)
            {
                case 1:
                    cash_Flow.cash_from = "购买商品";
                    break;
                case 2:
                    cash_Flow.cash_from = "退货";
                    break;
                case 3:
                    cash_Flow.cash_from = "财务修改";
                    break;
                case 4:
                    cash_Flow.cash_from = "营销活动";
                    break;
                case 5:
                    cash_Flow.cash_from = "推广赠送";
                    break;
                case 6:
                    cash_Flow.cash_from = "账户充值";
                    break;
                case 7:
                    cash_Flow.cash_from = "网上付款";
                    break;
                case 8:
                    cash_Flow.cash_from = "交易退款";
                    break;
                case 9:
                    cash_Flow.cash_from = "信用重置";
                    break;
                case 10:
                    cash_Flow.cash_from = "退货退分";
                    break;
            }

            switch (flowType)
            {
                case 0:
                    cash_Flow.cash_up = company.amount;
                    cash_Flow.cash_down = company.amount + value;
                    company.amount = cash_Flow.cash_down;
                    break;
                case 1:

                    int _points = Utils.StrToInt(Math.Floor(value).ToString(), 0);

                    Model.cash_flow cf = GetMaxModel(conn, trans, "", 1);

                    cash_Flow.cash_middle = _points;
                    cash_Flow.cash_up = cf != null ? cf.cash_down : 0;
                    cash_Flow.cash_down = cash_Flow.cash_up + _points;

                    Model.userconfig model = CacheHelper.Get<Model.userconfig>(DTKeys.CACHE_USER_CONFIG);
                    if (model == null)
                    {
                        CacheHelper.Insert(DTKeys.CACHE_USER_CONFIG, new userconfig().loadConfig(Utils.GetXmlMapPath(DTKeys.FILE_USER_XML_CONFING)),
                            Utils.GetXmlMapPath(DTKeys.FILE_USER_XML_CONFING));
                        model = CacheHelper.Get<Model.userconfig>(DTKeys.CACHE_USER_CONFIG);
                    }

                    //积分处理 财务修改积分时添加新增或扣除积分流水
                    if (flowCase == 3)
                    {
                        if (_points < 0)//财务扣除积分
                        {
                            
                        }
                        else//财务新增积分
                        {
                            
                        }
                    }
                    break;
            }

            cash_Flow.add_time = DateTime.Now;

            if (isUpdateErpBuddy)
            {
                if (buddyDal.Update(conn, trans, company))
                {
                    if (Add(conn, trans, cash_Flow) > 0)
                    {
                        return "";
                    }
                    else
                    {
                        return "流水添加失败003！";
                    }
                }
                else
                {
                    return "更新会员时失败，导致流水添加失败！";
                }
            }
            else//只有在会员使用微信、支付宝线上支付购物时，才不会更新会员的账户余额，不会产生实际上的流水。
            {
                if (Add(conn, trans, cash_Flow) > 0)
                {
                    decimal cash_down = cash_Flow.cash_down;
                    cash_Flow.cash_middle = cash_Flow.cash_middle * -1;
                    cash_Flow.cash_down = cash_Flow.cash_up;
                    cash_Flow.cash_up = cash_down;
                    cash_Flow.remark = "购买商品";
                    cash_Flow.cash_from = "购买商品";

                    if (Add(conn, trans, cash_Flow) > 0)
                    {
                        return "";
                    }
                    else
                    {
                        return "流水添加失败002！";
                    }
                }
                else
                {
                    return "流水添加失败001！";
                }
            }

        }


        public string FlowAction(SqlConnection conn, SqlTransaction trans, decimal order_amount, decimal user_amout, decimal user_pay_point, string companyCode, string operators,
            int _order_id, string orderNo, string remark)
        {
            company buddyDal = new company(databaseprefix);
            Model.company company = buddyDal.GetModel(conn, trans, companyCode);
            if (company == null)
            {
                return "没有找到会员编号为 " + companyCode + " 的会员信息！";
            }

            if (user_amout == 0 && order_amount > company.amount)
            {
                return "对不起，会员余额不足！";
            }

            Model.cash_flow cash_Flow = new Model.cash_flow();
            cash_Flow.cash_middle = user_amout;
            cash_Flow.company_code = companyCode;
            cash_Flow.operators = operators;
            cash_Flow.order_no = orderNo;
            cash_Flow.remark = remark;
            cash_Flow.flow_category = 0;
            cash_Flow.company_type = company.user_type == 1 ? "月结会员" : "普通会员";
            cash_Flow.company_group = new user_groups(databaseprefix).GetTitle(conn, trans, company.group_id);
            cash_Flow.company = "";
            cash_Flow.cash_from = "财务修改";
            cash_Flow.cash_up = company.amount;
            cash_Flow.cash_down = company.amount + user_amout;
            company.amount = company.amount + (user_amout - order_amount);
            cash_Flow.add_time = DateTime.Now;


            if (order_amount != user_amout)//当用户线下支付的钱不等于订单的钱时。产生流水。
            {
                if (buddyDal.Update(conn, trans, company))
                {
                    if (user_amout == 0)//如果账务输入的金额为0 ，订单直接扣会员余额。
                    {
                        decimal cash_down = cash_Flow.cash_down;
                        cash_Flow.cash_middle = order_amount * -1;
                        cash_Flow.cash_down = company.amount;
                        cash_Flow.cash_from = "购买商品";
                        cash_Flow.cash_up = cash_down;

                        if (Add(conn, trans, cash_Flow) > 0)
                        {
                            return "";
                        }
                        else
                        {
                            Utils.WriteLogs("流水添加失败002！", "订单号：" + orderNo, "", DTKeys.EXCEPION_PATH, orderNo);
                            return "流水添加失败002！";
                        }
                    }
                    else
                    {
                        if (Add(conn, trans, cash_Flow) > 0)
                        {
                            decimal cash_down = cash_Flow.cash_down;
                            cash_Flow.cash_middle = order_amount * -1;
                            cash_Flow.cash_down = company.amount;
                            cash_Flow.cash_from = "购买商品";
                            cash_Flow.cash_up = cash_down;

                            if (Add(conn, trans, cash_Flow) > 0)
                            {
                                return "";
                            }
                            else
                            {
                                Utils.WriteLogs("流水添加失败002！", "订单号：" + orderNo, "", DTKeys.EXCEPION_PATH, orderNo);
                                return "流水添加失败002！";
                            }
                        }
                        else
                        {
                            Utils.WriteLogs("流水添加失败003！", "订单号：" + orderNo, "", DTKeys.EXCEPION_PATH, orderNo);
                            return "流水添加失败003！";
                        }
                    }

                }
                else
                {
                    Utils.WriteLogs("更新会员时失败，导致流水添加失败", "订单号：" + orderNo, "", DTKeys.EXCEPION_PATH, orderNo);
                    return "更新会员时失败，导致流水添加失败！";
                }
            }
            else//会员账号余额不发生变化，才不会更新会员的账户余额，不会产生实际上的流水。
            {
                if (Add(conn, trans, cash_Flow) > 0)
                {
                    decimal cash_down = cash_Flow.cash_down;
                    cash_Flow.cash_middle = cash_Flow.cash_middle * -1;
                    cash_Flow.cash_down = cash_Flow.cash_up;
                    cash_Flow.cash_up = cash_down;
                    cash_Flow.cash_from = "购买商品";

                    if (Add(conn, trans, cash_Flow) > 0)
                    {
                        return "";
                    }
                    else
                    {
                        Utils.WriteLogs("流水添加失败002", "订单号：" + orderNo, "", DTKeys.EXCEPION_PATH, orderNo);
                        return "流水添加失败002！";
                    }
                }
                else
                {
                    Utils.WriteLogs("流水添加失败001", "订单号：" + orderNo, "", DTKeys.EXCEPION_PATH, orderNo);
                    return "流水添加失败001！";
                }
            }

        }

        /// <summary>
        /// 会员订单取消：流水处理
        /// </summary>
        /// <param name="companyCode">会员编号</param>
        /// <param name="flowCase">1购买商品2退货3财务修改4营销活动5推广赠送6账户充值7网上付款8交易退款9信用重置</param>
        /// <param name="flowType">0现金流水 2月结流水</param>
        /// <param name="operators">操作人</param>
        /// <param name="orderNo">订单编号</param>
        /// <param name="remark">备注</param>
        /// <param name="value">金额</param>
        /// <param name="conn">数据库已打开的链接</param>
        /// <param name="trans">数据处理已打开的事务</param>
        /// <param name="updateErpBuddy">是否更新会员表（只有在会员使用微信、支付宝线上支付购物时，才不会更新会员的账户余额，不会产生实际上的流水。）</param>
        /// <returns>返回空为处理成功，否则为失败原因。</returns>
        public string FlowActionCancelOrder(SqlConnection conn, SqlTransaction trans, decimal order_amount, decimal user_pay_point, string companyCode, string operators,
            int _order_id, string orderNo, string remark, int flowType)
        {
            company buddyDal = new company(databaseprefix);
            Model.company company = buddyDal.GetModel(conn, trans, companyCode);
            if (company == null)
            {
                return "没有找到会员编号为 " + companyCode + " 的会员信息！";
            }

            Model.cash_flow cash_Flow = new Model.cash_flow();
            cash_Flow.cash_middle = order_amount;
            cash_Flow.company_code = companyCode;
            cash_Flow.operators = operators;
            cash_Flow.order_no = orderNo;
            cash_Flow.remark = remark;
            cash_Flow.flow_category = flowType;
            cash_Flow.company_type = company.user_type == 1 ? "月结会员" : "普通会员";
            cash_Flow.company_group = new user_groups(databaseprefix).GetTitle(conn, trans, company.group_id);
            cash_Flow.company = "";
            cash_Flow.cash_from = "交易退款";

            cash_Flow.cash_up = company.amount;
            cash_Flow.cash_down = company.amount + order_amount;
            company.amount = cash_Flow.cash_down;

            cash_Flow.add_time = DateTime.Now;

            if (buddyDal.Update(conn, trans, company))
            {
                if (Add(conn, trans, cash_Flow) > 0)
                {
                    return "";
                }
                else
                {
                    Utils.WriteLogs("退款流水添加失败！", "订单号：" + orderNo, "", DTKeys.EXCEPION_PATH, orderNo);
                    return "退款流水添加失败！";
                }
            }
            else
            {
                Utils.WriteLogs("退款更新会员时失败，导致流水添加失败", "订单号：" + orderNo, "", DTKeys.EXCEPION_PATH, orderNo);
                return "退款更新会员时失败，导致流水添加失败！";
            }
        }



        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="cash_from">根所实际情况填写</param>
        /// <param name="flow_category">流类型 0现金 1积分 2月结</param>
        /// <param name="order_no">订单号</param>
        public Model.cash_flow GetModel(int flow_category, string order_no, string cash_from)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.cash_flow model = new Model.cash_flow();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "cash_flow");
            strSql.Append(" where flow_category=@flow_category and order_no=@order_no  and cash_from=@cash_from");
            SqlParameter[] parameters = {
                        new SqlParameter("@flow_category", SqlDbType.Int,10),
            new SqlParameter("@order_no", SqlDbType.NVarChar,50),
            new SqlParameter("@cash_from", SqlDbType.NVarChar,50)};
            parameters[0].Value = flow_category;
            parameters[1].Value = order_no;
            parameters[2].Value = cash_from;

            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string order_no, string cash_from, int flow_category)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from  " + databaseprefix + "cash_flow");
            strSql.Append(" where order_no=@order_no and flow_category =@flow_category  and cash_from=@cash_from");
            SqlParameter[] parameters = {
                        new SqlParameter("@order_no", SqlDbType.NVarChar,50),
                        new SqlParameter("@flow_category", SqlDbType.Int,4),
                        new SqlParameter("@cash_from", SqlDbType.NVarChar,50)};
            parameters[0].Value = order_no;
            parameters[1].Value = flow_category;
            parameters[2].Value = cash_from;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        public Model.cash_flow GetMaxModel(SqlConnection conn, SqlTransaction trans, string erpCode, int cash_flow_type, string add_time)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.cash_flow model = new Model.cash_flow();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "cash_flow");
            strSql.Append(" where company_code=@company_code and flow_category=@flow_category and add_time <='" + add_time + "' order by add_time desc");
            SqlParameter[] parameters = {
                        new SqlParameter("@company_code", SqlDbType.VarChar,50),
                new SqlParameter("@flow_category", SqlDbType.Int,10)};
            parameters[0].Value = erpCode;
            parameters[1].Value = cash_flow_type;

            DataTable dt = DbHelperSQL.Query(conn, trans, strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(SqlConnection conn, SqlTransaction trans, string add_time, string company_code)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id   FROM " + databaseprefix + "cash_flow where company_code = '" + company_code + "' and add_time > '" + add_time + "' and flow_category = 1 order by id ");
            return DbHelperSQL.Query(conn, trans, strSql.ToString());
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(SqlConnection conn, SqlTransaction trans, int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "cash_flow set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString()) > 0;
        }

        /// <summary>
        /// 返回指定列cash_middle的值
        /// </summary>
        public decimal GetCashMiddleValue(SqlConnection conn, SqlTransaction trans, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 cash_middle from " + databaseprefix + "cash_flow");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where id>0 and " + strWhere);
            }

            string v = Convert.ToString(DbHelperSQL.GetSingle(conn, trans, strSql.ToString()));
            if (string.IsNullOrEmpty(v))
            {
                return Utils.StrToDecimal(v, 0);
            }
            return 0;
        }

        /// <summary>
        /// 现金流水重置系统功能
        /// </summary>
        public void ForSystem()
        {
            StringBuilder _sb = new StringBuilder();

            //_sb.Append("select company_code,order_no,cash_middle,cash_from,remark, count(*) c  ");
            //_sb.Append("from (select * from dt_cash_flow  ");
            //_sb.Append("where add_time >= '2021-03-15 17:29:15.003' and flow_category = 0 and order_no != '') tab ");
            //_sb.Append("group by company_code, order_no,cash_middle,cash_from,remark having(count(*))>1 order by company_code ");

            _sb.Append("select company_code,order_no,cash_middle,cash_from,remark, count(*) c ");
            _sb.Append("from dt_cash_flow   ");
            _sb.Append("where flow_category = 0 and order_no != '' ");
            _sb.Append("group by company_code, order_no,cash_middle,cash_from,remark ");
            _sb.Append("having(count(*))>1 order by company_code ");

            DataSet dataSet = DbHelperSQL.Query(_sb.ToString());

            if (Utils.CheckDataSet(dataSet))
            {
                foreach (DataRow dr in dataSet.Tables[0].Rows)
                {
                    DbHelperSQL.ExecuteSql("delete from dt_cash_flow where id in (select top " + (int.Parse(dr["c"].ToString()) - 1) + " id from dt_cash_flow where flow_category = 0 and company_code = '" + dr["company_code"] + "' and order_no = '" + dr["order_no"] + "' order by id desc);");

                    DataSet _ds = DbHelperSQL.Query("select id,cash_middle,cash_down,cash_up from dt_cash_flow where flow_category = 0 and company_code = '" + dr["company_code"] + "' order by id ");

                    if (Utils.CheckDataSet(_ds))
                    {
                        for (int i = 1; i < _ds.Tables[0].Rows.Count; i++)
                        {
                            DbHelperSQL.ExecuteSql("update dt_cash_flow set cash_up = " + _ds.Tables[0].Rows[i - 1]["cash_down"] + ",cash_down = " + _ds.Tables[0].Rows[i - 1]["cash_down"] + "+cash_middle where id = " + int.Parse(_ds.Tables[0].Rows[i]["id"].ToString()));
                        }
                    }

                }
            }

        }

        #endregion
    }
}

